package fh.app4stud.mensa.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

/**
 * Creates Database with table "menu" from an xml file.
 * 
 * Created by IntelliJ IDEA. User: ivan plese Date: 22.04.11 Time: 20:31 To
 * change this template use File | Settings | File Templates.
 */
public class MenuDbAdapter {

	public static final String KEY_LOC_ID = "_loc_id";
	public static final String KEY_LOC_NAME = "loc_name";
	public static final String KEY_DAY_ID = "_day_id";
	public static final String KEY_DAY_NAME = "day_name";
	public static final String KEY_MENU_ID = "_menu_id";
	public static final String KEY_MENU_NAME = "menu_name";
	public static final String KEY_PRICE = "price";
	public static final String KEY_ITEM = "item";

	public static final String KEY_CITY = "city";
	public static final String KEY_STREET = "street";
	public static final String KEY_HOUSE_NR = "house_nr";
	public static final String KEY_ZIP = "zip_code";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";

	private static final String TAG = "DATA BASE MenuDbAdapter";

	private static final String DATABASE_NAME = "data";
	private static final String DATABASE_TABLE_LOC = "locations";
	private static final String DATABASE_TABLE = "menu";
	private static final int DATABASE_VERSION = 2;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DATABASE_CREATE = "create table menu ("
			+ "_id integer primary key autoincrement, "
			+ "_loc_id integer not null," + "loc_name text not null,"
			+ "_day_id integer not null," + "day_name text not null,"
			+ "_menu_id integer not null," + "menu_name text not null,"
			+ "price text," + "item text not null);";

	private static final String DATABASE_CREATE_LOC = "create table "
			+ DATABASE_TABLE_LOC + " ("
			+ "_id integer primary key autoincrement," 
			
			+ "loc_name text not null," 
			+ "city text not null,"
			+ "street text not null," + "house_nr integer not null,"
			+ "zip_code integer not null," + "latitude real not null,"
			+ "longitude real not null);";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_LOC);
			db.execSQL(DATABASE_CREATE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS menu;");
			db.execSQL("DROP TABLE IF EXISTS locations;");
			onCreate(db);
		}
	}

	/**
	 * Constructor - /takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 * the Context within which to work
	 */
	public MenuDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the items database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws android.database.SQLException
	 *             if the database could be neither opened or created
	 */
	public MenuDbAdapter open() throws SQLException {
		Log.i(TAG, " Constructor of data base adapter ");
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * Create a new menu item using the title and body provided. If the menu is
	 * successfully created return the new rowId for that menu, otherwise return
	 * a -1 to indicate failure.
	 * 
	 * @param loc_id
	 *            location id
	 * @param loc_name
	 *            location name
	 * @param day_id
	 *            day id
	 * @param day_name
	 *            day name
	 * @param menu_id
	 *            menu id
	 * @param menu_name
	 *            menu name
	 * @param price
	 *            price
	 * @param item
	 *            item name
	 * @return cursor with all the data in the database
	 */
	public long createItem(int loc_id, String loc_name, int day_id,
			String day_name, int menu_id, String menu_name, String price,
			String item) {
		Log.i(TAG, "In Create Item");
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_LOC_ID, loc_id);
		initialValues.put(KEY_LOC_NAME, loc_name);
		initialValues.put(KEY_DAY_ID, day_id);
		initialValues.put(KEY_DAY_NAME, day_name);
		initialValues.put(KEY_MENU_ID, menu_id);
		initialValues.put(KEY_MENU_NAME, menu_name);
		initialValues.put(KEY_PRICE, price);
		initialValues.put(KEY_ITEM, item);

		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}

	public long createLocation(String loc_name, String city, String street,
			int house_nr, int zip, float lat, float lon) {
		Log.i(TAG, "In Create Locations");
		ContentValues initialValues = new ContentValues();

		initialValues.put(KEY_LOC_NAME, loc_name);
		initialValues.put(KEY_CITY, city);
		initialValues.put(KEY_STREET, street);
		initialValues.put(KEY_HOUSE_NR, house_nr);
		initialValues.put(KEY_ZIP, zip);
		initialValues.put(KEY_LATITUDE, lat);
		initialValues.put(KEY_LONGITUDE, lon);

		return mDb.insert(DATABASE_TABLE_LOC, null, initialValues);
	}

	// SELECT * FROM "test_table" WHERE "lastname" = 'last1' AND address='add2';
	public Cursor rawQueryByDayAndLoc(String location, int dayId) {
		Cursor mCursor = mDb.rawQuery("SELECT " + KEY_DAY_NAME + ","
				+ KEY_MENU_ID + "," + KEY_MENU_NAME + "," + KEY_PRICE + ","
				+ KEY_ITEM + " FROM ( " +

				"SELECT " + KEY_DAY_NAME + "," + KEY_LOC_ID + "," + KEY_DAY_ID
				+ "," + KEY_MENU_ID + "," + KEY_MENU_NAME + "," + KEY_PRICE
				+ "," + KEY_ITEM + " FROM " + DATABASE_TABLE + " WHERE "
				+ KEY_LOC_NAME + "=?) " +

				"WHERE " + KEY_DAY_ID + "=" + dayId,

		new String[] { location });
		return mCursor;
	}

	public Cursor fetchAllLocations() {

		return mDb.query(DATABASE_TABLE_LOC, new String[] { KEY_LOC_NAME,
				KEY_CITY, KEY_STREET, KEY_HOUSE_NR, KEY_ZIP, KEY_LONGITUDE,
				KEY_LATITUDE }, null, null, null, null, null);
	}

	public Cursor fetchLocationsByCity(String city) {
		Cursor mCursor = mDb.rawQuery("SELECT " + "_id" + "," + KEY_LOC_NAME
				+ "," + KEY_STREET + "," + KEY_HOUSE_NR + "," + KEY_ZIP + ","
				+ KEY_LATITUDE + "," + KEY_LONGITUDE

				+ " FROM " + DATABASE_TABLE_LOC + " WHERE " + KEY_CITY + "=?",

		new String[] { city });
		return mCursor;
	}

	public Cursor fetchCityNames() {

		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		String qery = SQLiteQueryBuilder.buildQueryString(true,
				DATABASE_TABLE_LOC, new String[] { KEY_CITY }, null, null,
				null, KEY_CITY + " ASC", null);
		Cursor mCursor = mDb.rawQuery(qery, null);
		return mCursor;

	}

	public void deleteRecords(String tableName) {
		// every monday or sunday the records are replaced with new ones
		mDb.execSQL("DROP TABLE IF EXISTS " + tableName);
		mDb.execSQL(DATABASE_CREATE);
	}

	public void deleteLocations() {
		mDb.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_LOC);
		mDb.execSQL(DATABASE_CREATE_LOC);
	}
}
